package dao;

import java.sql.*;
import java.util.Objects;
import java.util.Vector;

import org.eclipse.jdt.annotation.NonNull;

import bean.User;

public class UserDAO {
    private Connection conn;
    private PreparedStatement ps;
    private ResultSet rs;

    public UserDAO(@NonNull Connection conn) {
		this.conn = conn;
	}

	public boolean insert(@NonNull EmployeeDAO employeeDAO, @NonNull User user) throws SQLException {
        int status = user.getStatus();
        int employeeId = user.getEmployeeId();
        int id = selectIdByEmployeeId(employeeId);
        if (status < 0 || status > 2 || id > 0) {
            return false;
        }
        if (Objects.isNull(employeeDAO.selectById(employeeId))) {
            return false;
        }
        ps = conn.prepareStatement("INSERT INTO USERS VALUES(?,?,?,?)");
        ps.setString(1, user.getUserName());
        ps.setString(2, user.getPassword());
        ps.setInt(3, status);
        ps.setInt(4, employeeId);
        boolean state = ps.executeUpdate() == 1;
        ps.close();
        return state;
    }

    public User selectById(int id) throws SQLException {
        User user = null;
        if (id < 1) {
            return user;
        }
        ps = conn.prepareStatement("SELECT USER_ID,USER_NAME,STATUS,EMPLOYEE_ID FROM USERS WHERE USER_ID=?");
        ps.setInt(1, id);
        rs = ps.executeQuery();
        if (rs.next()) {
            String name = rs.getString("USER_NAME");
            int status = rs.getInt("STATUS");
            int employeeId = rs.getInt("EMPLOYEE_ID");
            user = new User(id, name, status, employeeId);
        }
        rs.close();
        ps.close();
        return user;
    }

    public User selectByNameAndPassword(@NonNull String name, @NonNull String password) throws SQLException {
        User user = null;
        int id, status;
        if (name.length() < 1 || password.length() < 1) {
            return user;
        }
        ps = conn.prepareStatement("SELECT USER_ID,STATUS FROM USERS WHERE USER_NAME=? AND PASSWORD=?");
        ps.setString(1, name);
        ps.setString(2, password);
        rs = ps.executeQuery();
        if (rs.next()) {
            id = rs.getInt("USER_ID");
            status = rs.getInt("STATUS");
            user = new User(id, status);
        }
        rs.close();
        ps.close();
        return user;
    }

    public int selectUidCountByUi(int status) throws SQLException {
        int count = -1;
        if (status < 0 || status > 2) {
            return count;
        }
        ps = conn.prepareStatement("SELECT COUNT(USER_ID) FROM USERS WHERE STATUS=?");
        ps.setInt(1, status);
        rs = ps.executeQuery();
        if (rs.next()) {
            count = rs.getInt(1);
        }
        rs.close();
        ps.close();
        return count;
    }

    public int selectIdByEmployeeId(int employeeId) throws SQLException {
        int id = -1;
        if (employeeId < 1) {
            return id;
        }
        ps = conn.prepareStatement("SELECT USER_ID FROM USERS WHERE EMPLOYEE_ID=?");
        ps.setInt(1, employeeId);
        rs = ps.executeQuery();
        if (rs.next()) {
            id = rs.getInt("USER_ID");
        }
        rs.close();
        ps.close();
        return id;
    }

    public Vector<User> selectAll() throws SQLException {
        Vector<User> users = new Vector<>();
        ps = conn.prepareStatement("SELECT USER_ID,USER_NAME,STATUS,EMPLOYEE_ID FROM USERS");
        rs = ps.executeQuery();
        while (rs.next()) {
            int id = rs.getInt("USER_ID");
            String name = rs.getString("USER_NAME");
            int status = rs.getInt("STATUS");
            int employeeId = rs.getInt("EMPLOYEE_ID");
            User user = new User(id, name, status, employeeId);
            users.addElement(user);
        }
        rs.close();
        ps.close();
        return users;
    }

    public boolean update(@NonNull User user) throws SQLException {
        ps = conn.prepareStatement("UPDATE USERS SET PASSWORD=?,STATUS=? WHERE USER_ID=?");
        ps.setString(1, user.getPassword());
        ps.setInt(2, user.getStatus());
        ps.setInt(3, user.getUserId());
        boolean state = ps.executeUpdate() == 1;
        ps.close();
        return state;
    }

    public boolean delete(int id) throws SQLException {
        if (id < 1) {
            return false;
        }
        ps = conn.prepareStatement("DELETE FROM USERS WHERE USER_ID=?");
        ps.setInt(1, id);
        boolean state = ps.executeUpdate() == 1;
        ps.close();
        return state;
    }
}
